package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.ZiXun;
import com.ysd.util.DBManager;

public class ZiXunDaoImpl {
	public List<ZiXun> getZiXunList(){
		List<ZiXun> list = new ArrayList<ZiXun>(); // 定义一个ArrayList
		Connection conn = DBManager.getConnection();// 获得连接
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement(); //创建Statement对象，这个对象作用是执行sql语句
			String sql = "select * from zxfenlei";//执行查询的sql语句
			System.out.print(sql);
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int zXFEbLeiId = rs.getInt("zXFEbLeiId");
				String zXNames = rs.getString("zXNames");
				ZiXun zixun = new ZiXun(zXFEbLeiId, zXNames);
				list.add(zixun);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//异常现象
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);//关闭连接
		}
	}
	
	public List<ZiXun> getZiXunList(int page,int size){
		List<ZiXun> list = new ArrayList<ZiXun>(); // 定义一个ArrayList
		Connection conn = DBManager.getConnection();// 获得连接
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement(); //创建Statement对象，这个对象作用是执行sql语句
			String sql = "select * from zxfenlei limit "+(page-1)*size+","+size;//执行查询的sql语句
			System.out.print(sql);
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int zXFEbLeiId = rs.getInt("zXFEbLeiId");
				String zXNames = rs.getString("zXNames");
				ZiXun zixun = new ZiXun(zXFEbLeiId, zXNames);
				list.add(zixun);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//异常现象
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);//关闭连接
		}
	}
	public int delzX(int zxid){
		return DBManager.executeUpdate("DELETE from zxfenlei WHERE zXFEbLeiID="+zxid);
	}
	//**添加资讯分类，返回数据库受影响行数
	 
	public int addConSult(ZiXun x) { //传一个资讯分类对象
		return DBManager.executeUpdate("insert into zxfenlei VALUES(null,'"+x.getzXNames()+"')");
	}
	public List<ZiXun> getFenLeiByName(String name){
    	List<ZiXun> zList = new ArrayList<ZiXun>();
    	Connection conn = DBManager.getConnection();
    	Statement st = null;
    	ResultSet rs = null;
    	
    	try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT * FROM zxfenlei WHERE zXNames='"+name+"'");
			while(rs.next()) {
				int pId = rs.getInt(1);
				String pName = rs.getString(2);
				ZiXun z = new ZiXun(pId, pName);
				zList.add(z);
			}
			return zList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
    }

	
	public ZiXun getZiXun(int xgid) {
		ZiXun xg = null;
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT * FROM zxfenlei WHERE zXFEbLeiID="+xgid);
			while(rs.next()) {
				int zXFEbLeiId = rs.getInt(1); //1第一列
				String zXNames = rs.getString(2);//2第二列
				xg = new ZiXun(zXFEbLeiId, zXNames);
			}
			return xg;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	public int updateconsult(ZiXun zx) {
		String sql = "UPDATE zxfenlei set zXNames='"+zx.getzXNames()+"' where zXFEbLeiID="+zx.getzXFEbLeiId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	/**
	 * 批量删除
	 * @param zXFEbLeiIDs
	 * @return
	 */
	public int deletezXFEbLeiIDs(String zXFEbLeiIDs) {
		String sql = "delete from zxfenlei where zXFEbLeiID in("+zXFEbLeiIDs+")";
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
}
